This application relates generally to database query optimization and in particular to using plan constraints for limiting plan choices for database queries.
Databases store large amount of data that is processed in response to database queries. Typically databases generate an execution plan for the queries. The execution plan comprises various operators and order of execution of these operators to process the database query. A database query optimizer typically evaluates several execution plans to select a particular execution plan for execution. The query optimizer selects the execution plan that the optimizer determines as the best execution plan for executing the database query. An execution plan is also referred to herein as a execution plan, a query plan or a plan.
Generating the optimal execution plan is a complex process that can take significant amount of time and resources depending on the complexity of the query being optimized. Therefore, query optimizers use heuristics to arrive at a solution faster even though the solution may not be optimal. However, as a result, optimizers can make incorrect decisions while generating the execution plan for a query.
Database experts often have knowledge about database queries that may be difficult for a query optimizer to determine. Therefore, database experts may be able to improve upon an execution plan generated by an optimizer. Some optimizers allow users to provide optimizer hints. However, conventional systems provide only a very limited expressive power for providing optimizer hints. Furthermore, the hints provided either drastically limit the plan choices considered by the optimizer drastically or are entirely ignored by the optimizer. Therefore, conventional techniques for allowing users to specify ways to help optimizers are limited in their expressive power and their ability to help the optimizer generate optimal plans.